/**
 * 
 */
package org.compiere.report;

import java.io.File;
import java.io.FileOutputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import org.adempiere.exceptions.AdempiereException;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.compiere.model.MAsset;
import org.compiere.model.MAttachmentEntry;
import org.compiere.model.MBPartner;
import org.compiere.model.MOrg;
import org.compiere.model.Query;
import org.compiere.process.ProcessInfo;
import org.compiere.process.ProcessInfoParameter;
import org.compiere.process.SvrProcess;
import org.compiere.util.DB;
import org.compiere.util.Env;
import org.compiere.util.Msg;
import org.eevolution.model.MHRJob;
import extend.org.compiere.utils.Util;

/**
 * @author E.Seidegali
 *
 */
public class A_Asset_Form_Inv16 extends SvrProcess {
	/*Current context*/
	private Properties m_ctx ;
	
	private ProcessInfo pi ;
	
	Integer p_mol, p_Accountant_ID;
	
	Date p_DateDoc, p_DateDocBase, p_DateInvBegin, p_DateInvEnd;
	
	String ResultPath, p_DocumentBaseNo, p_DocumentNo;
	
	/*
	* Prepare - e.g., get Parameters.
	*/
	@Override
	protected void prepare() {
	log.info( "");
	m_ctx = Env. getCtx();
	pi = getProcessInfo();
	ProcessInfoParameter[] para = getParameter();
	for (int i = 0; i < para.length; i++) {
		String name = para[i].getParameterName();
		if (para[i].getParameter() == null)
			;
		else if (name.equals("mol"))
			p_mol = para[i].getParameterAsInt();
		else if (name.equals("DocumentNo"))
			p_DocumentNo = (String) para[i].getParameter();
		else if (name.equals("DateDoc"))
			p_DateDoc = (Date) para[i].getParameter();
		else if (name.equals("DateInv")) {			
			p_DateInvBegin = (Date) para[i].getParameter();
			p_DateInvEnd = (Date) para[i].getParameter_To();
		}
		else if (name.equals("DateDocBase"))
			p_DateDocBase = (Date) para[i].getParameter();
		else if (name.equals("Accountant_ID"))
			p_Accountant_ID = para[i].getParameterAsInt();
		else
			log.log(Level. SEVERE, "Unknown Parameter: " + name);
		}
	}
	
	@Override
	protected String doIt() throws Exception {
		//File path
		ResultPath = "Form_Inv16_" ;
		Integer pathI = 0;
		boolean flag = true ;
		while (flag) {
			File f = new File(Util.localFilePath + ResultPath + ".xlsx");
			try {
				if (f.exists() && !f.delete()) {
					ResultPath = ResultPath + "(" ;
					ResultPath = ResultPath .substring(0, 12) + (++pathI) + ")";
				} else {
					flag = false;
				}
			} catch (Exception e) {
				e.printStackTrace();
				ResultPath = ResultPath + "(" + (++pathI) + ")";
			}
		}
		ResultPath = Util.localFilePath + ResultPath + ".xlsx" ;
		MAttachmentEntry entry = Util. getAttachment(pi, m_ctx, ResultPath);
		if (entry == null)
			throw new AdempiereException(Msg.translate (m_ctx , "NotFoundTemplate"));

		Workbook wb = new XSSFWorkbook(entry.getInputStream());
		Sheet sheet = wb.getSheetAt(0);
		Row R = null;
		Cell C = null;
		
		//Cell Styling
		CellStyle mergeStyle = wb.createCellStyle();
		mergeStyle.setBorderBottom(( short)1);
		mergeStyle.setBorderTop(( short)1);
		mergeStyle.setBorderLeft(( short)1);
		mergeStyle.setBorderRight(( short)1);
		Font mergeFont = wb.getFontAt(sheet.getRow(9).getCell(0).getCellStyle().getFontIndex());
		mergeStyle.setFont(mergeFont);
		mergeStyle.setWrapText( true);
		mergeStyle.setVerticalAlignment(CellStyle. VERTICAL_CENTER);
		
		//Header
		sheet.getRow(9).getCell(20).setCellValue(MOrg. get(m_ctx, Env.getAD_Org_ID(m_ctx)).getName());
		sheet.getRow(9).getCell(57).setCellValue((new MBPartner(m_ctx , MOrg.get(m_ctx, Env.getAD_Org_ID(m_ctx )).getLinkedC_BPartner_ID(get_TrxName()), get_TrxName()).getFI_BIN_IIN()));
		
		//Document
		sheet.getRow(13).getCell(49).setCellValue(p_DocumentNo);
		sheet.getRow(13).getCell(57).setCellValue((new SimpleDateFormat("dd.MM.yyyy").format(p_DateDoc)));
		
		sheet.getRow(14).getCell(26).setCellValue((new SimpleDateFormat("dd").format(p_DateDoc)));
		sheet.getRow(14).getCell(29).setCellValue((new SimpleDateFormat("MMMMM").format(p_DateDoc)));
		sheet.getRow(14).getCell(35).setCellValue((new SimpleDateFormat("yyyy").format(p_DateDoc)));
		
		//Document based
		sheet.getRow(16).getCell(21).setCellValue((new SimpleDateFormat("dd").format(p_DateDocBase)));
		sheet.getRow(16).getCell(24).setCellValue((new SimpleDateFormat("MMMMM").format(p_DateDocBase)));
		sheet.getRow(16).getCell(30).setCellValue((new SimpleDateFormat("yyyy").format(p_DateDocBase)));
		
		//Begin inventory
		sheet.getRow(20).getCell(11).setCellValue((new SimpleDateFormat("dd").format(p_DateInvBegin)));
		sheet.getRow(20).getCell(14).setCellValue((new SimpleDateFormat("MMMMM").format(p_DateInvBegin)));
		sheet.getRow(20).getCell(22).setCellValue((new SimpleDateFormat("yyyy").format(p_DateInvBegin)));
		//End inventory
		sheet.getRow(20).getCell(48).setCellValue((new SimpleDateFormat("dd").format(p_DateInvEnd)));
		sheet.getRow(20).getCell(51).setCellValue((new SimpleDateFormat("MMMMM").format(p_DateInvEnd)));
		sheet.getRow(20).getCell(59).setCellValue((new SimpleDateFormat("yyyy").format(p_DateInvEnd)));
		//MOL
		String sqlheader = "SELECT HR_Job_ID FROM HR_Employee e WHERE e.C_BPartner_ID = " + p_mol;
		int id = DB.getSQLValue(get_TrxName(), sqlheader);
		sheet.getRow(17).getCell(21).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(17).getCell(40).setCellValue((new MBPartner(m_ctx, p_mol, get_TrxName()).getName()));
		
		sheet.getRow(37).getCell(21).setCellValue((new MHRJob(m_ctx, id, get_TrxName()).getName()));
		sheet.getRow(37).getCell(40).setCellValue((new MBPartner(m_ctx, p_mol, get_TrxName()).getName()));
		//Accountant
		sheet.getRow(33).getCell(17).setCellValue((new MBPartner(m_ctx, p_Accountant_ID, get_TrxName()).getName()));
		
		//Positions
		int row = 28;
		int no = 0;
		
		String whereClauseFinal = "AD_Client_ID = ?" 
					+ " AND AD_Org_ID = ?" 
					+ " AND C_BPartnerSR_ID = ?" 
					+ " AND IsActive = 'Y'";
		List<MAsset> assets = new Query(getCtx(), MAsset.Table_Name, whereClauseFinal, get_TrxName())
		.setParameters(Env.getAD_Client_ID(m_ctx), Env.getAD_Org_ID(m_ctx), p_mol)
		.list();
		
		for(MAsset asset: assets) {
			//Shifting and Merging
			sheet.shiftRows(row, row + 26, 1);
			sheet.addMergedRegion(new CellRangeAddress(row, row, 0, 2));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 3, 14));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 15, 20));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 21, 25));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 26, 30));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 31, 35));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 36, 42));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 43, 47));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 48, 53));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 54, 58));
			sheet.addMergedRegion(new CellRangeAddress(row, row, 59, 64));
			
			R = sheet.createRow(row);
			for(int i = 0; i <= 64; i++){
				C = R.createCell(i);
				C.setCellStyle(mergeStyle);
			}
			
			sheet.getRow(row).getCell(0).setCellValue(++no);
			sheet.getRow(row).getCell(3).setCellValue(asset.getName());
			sheet.getRow(row).getCell(15).setCellValue(asset.getAssetServiceDate() == null ? "" : (new SimpleDateFormat("dd.MM.yyyy").format(asset.getAssetServiceDate())));
			sheet.getRow(row).getCell(21).setCellValue(asset.getA_Asset_CreateDate() == null ? "" : (new SimpleDateFormat("dd.MM.yyyy").format(asset.getA_Asset_CreateDate())));
			sheet.getRow(row).getCell(26).setCellValue(asset.getValue());
			sheet.getRow(row).getCell(31).setCellValue(asset.getSerNo());
			sheet.getRow(row).getCell(43).setCellValue(0);
			sheet.getRow(row).getCell(48).setCellValue(0);
			sheet.getRow(row).getCell(54).setCellValue(0);
			sheet.getRow(row).getCell(59).setCellValue(0);
			
			row++;
		}
		
		sheet.getRow(row).getCell(43).setCellFormula("SUM(AR29:AR" + row + ")");
		sheet.getRow(row).getCell(48).setCellFormula("SUM(AW29:AW" + row + ")");
		sheet.getRow(row).getCell(54).setCellFormula("SUM(BC29:BC" + row + ")");
		sheet.getRow(row).getCell(59).setCellFormula("SUM(BH29:BH" + row + ")");
		
		//--------------------------------------------------------------------------
		//Write the output to a file
		FileOutputStream fileOut = new FileOutputStream(ResultPath);
		wb.write(fileOut);
		fileOut.close();
		
		//Open the file with the application associated
		Util. launchFile(new File(ResultPath));
		
		return Msg.translate(m_ctx, "Success");
	}	
}
